package database.mediator;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import distributedRE.model.DataBase;
import distributedRE.model.Document;
import distributedRE.model.Query;
import distributedRE.model.Relation;
import distributedRE.model.Tuple;

public class MySQLMediator implements MediatorInterface{

	
	private Connection conn;
	
	public MySQLMediator(){
		conn=null;
	}
	
	public void closeConnection() {
		
		try {
			getConnection().close();
	    	System.out.println("Disconnected from database");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	private Connection getConnection() {
		if (conn == null){
			openConnection();
		}
		return conn;
	}

	public void openConnection() {
	    
		conn = null;
		String url = "jdbc:mysql://db-files:3306/";
	    String dbName = "distributedRE";
	    String driver = "com.mysql.jdbc.Driver";
	    String userName = "pjbarrio"; 
	    String password = "test456";
	    try {
	      
	    	Class.forName(driver).newInstance();
	    	conn = DriverManager.getConnection(url+dbName,userName,password);
	    	System.out.println("Connected to the database");
    	
	    } catch (Exception e) {
    		e.printStackTrace();
    	}
	}

	private void performTransaction(String todo) {
		try {
    		java.sql.Statement s = getConnection().createStatement();
    		s.executeUpdate (todo);
    	}
    	catch (Exception e) {
    		e.printStackTrace();
    	}     
		
	}
	
	public void writeAdaptiveStep(int experimentId, int sessionId,
			long adjustmentId, long measuredTime) {
    		      
    	String todo = ("INSERT into AdaptiveStep " +
              "(e_id, s_id, a_id, time) values (" + experimentId + ","+sessionId + "," + adjustmentId + "," + measuredTime +")");

    	performTransaction(todo);
    	
	}



	public void writeDocumentTuple(long documentId,
			String informationExtractorId, long r_id, long tupleId) {
		
		String todo = ("INSERT into DocumentTuples " +
	              "(d_id, ie_id, r_id, t_id) values (" + documentId + ","+ informationExtractorId + "," + r_id + "," + tupleId +")");

    	performTransaction(todo);

	}

	public void writeEndDate(Integer experimentId, String dateTime) {
		
	
		String todo = ("UPDATE Experiment SET Experiment.end_date=" + format(dateTime) + " WHERE Experiment.e_id=" + experimentId);

		performTransaction(todo);
		
	}

	public void writeExperiment(Integer experimentId, long relationId,
			String dateTime, String environment, String qdbs,
			String searchEngine, String stopPolicy, String adjustment,
			String rankingGenerator) {
	
		String todo = ("INSERT into Experiment " +
	              "(e_id, r_id, start_date, Env_Conf, qdbs, search_engine, stop_policy, adjustment, ranking_generator)" +
	              " values (" + experimentId + ","+ relationId + "," + format(dateTime) + "," + format(environment) + "," + format(qdbs) + "," + format(searchEngine) + "," +
	              format(stopPolicy) + "," + format(adjustment) + "," + format(rankingGenerator) +")");

		performTransaction(todo);
		
	}

	private String format(String stringValue) {
		
		//TODO Check if "\'" is OK.
		
		stringValue = stringValue.replace("'", "\\'");
		
		return ("'" + stringValue + "'");
	}

	public void writeIEonDocument(String informationExtractorId,
			long documentId, boolean useful, long measuredTime) {
		
		String val = "0";
		
		if (useful)
			val="1";
		
		String todo = ("INSERT into IEonDocument " +
	              "(ie_id, d_id, useful, time) values (" + informationExtractorId + ","+ documentId + "," + val + "," + measuredTime +")");

		performTransaction(todo);
		
		
	}

	public void writeInitialPrecision(long queryId, long databaseId,
			double initialPrecision) {
		
		String todo = ("INSERT INTO `InitialPrecision` (`q_id`, `db_id`, `precision`) VALUES (" + 
				queryId + "," + databaseId + "," + initialPrecision + ")");

		performTransaction(todo);
		
	}

	public void writeQueryResults(long queryId, long databaseId,
			int experimentId, int sessionId, long documentId, int position,
			Comparable score) {
		
		String todo = ("INSERT into QueryResults " +
	              "(q_id, db_id, e_id, s_id, d_id, position, score) values " +
	              "(" + queryId + ","+ databaseId + "," + experimentId + "," + sessionId + "," + documentId + "," + position + "," + format(score.toString()) +")");

		performTransaction(todo);
		
	}

	public void writeQuerySession(long queryId, long databaseId,
			int experimentId, int sessionId, long measuredTime) {
		
		String todo = ("INSERT into QuerySession " +
	              "(q_id, db_id, e_id, s_id, time) values (" + queryId + ","+ databaseId + "," + experimentId + "," + sessionId + "," + measuredTime +")");

		performTransaction(todo);
		
	}

	public void writeSession(int experimentId, int sessionId, long measuredTime) {
		
		String todo = ("INSERT into Session " +
	              "(e_id, s_id, time) values (" + experimentId + ","+ sessionId + "," + 0 +")");

		performTransaction(todo);
		
	}

	public void writeSessionResult(int experimentId, int sessionId,
			long queryId, long databaseId, int position, Comparable<?> score) {
		
		String todo = ("INSERT into SessionResult " +
	              "(e_id, s_id, q_id, db_id, position, score) values " +
	              "(" + experimentId + ","+ sessionId + "," + queryId + "," + databaseId + "," + position + "," + format(score.toString()) +")");

		performTransaction(todo);
		
	}

	public void writeTuple(long informationExtractorId, long relationId, long tupleId, String text, long ie_rootId, long r_rootId, long rootId) {
		
		String todo = ("INSERT into Tuple " +
	              "(ie_id, r_id, t_id, text, troot_ie_id, troot_r_id, troot_id) values (" + informationExtractorId + "," +
	              relationId + ","+ tupleId + "," + format(text) + "," + ie_rootId + "," + r_rootId + "," + rootId +")");

		performTransaction(todo);
		
	}
	
	public void writeTuple(String informationExtractorId, String relationId, String tupleId, String text) {
		
		String todo = ("INSERT into Tuple " +
	              "(ie_id r_id, t_id, text) values (" + informationExtractorId + "," + 
	              relationId + ","+ tupleId + "," + format(text) + ")");

		performTransaction(todo);
		
	}

	public void writeUsedIE(Integer idExperiment, String informationExtractorId) {
		
		String todo = ("INSERT into IEusedinE " +
	              "(e_id, ie_id) values " + "(" + idExperiment + "," + informationExtractorId + ")");

		performTransaction(todo);
		
	}

	public void writeAdjustment(int adjustmentId, String description, String frequency) {
		
		String todo = ("INSERT into Adjustment " +
	              "(a_id, description, frequency) values " + "(" + adjustmentId + "," + format(description) + "," + format(frequency) + ")");

		performTransaction(todo);
	}

	public void writeDatabase(int databaseId, String url, int sampleSize, int words_number, int documents_number,
			String cs_path, String index_path, String local_path, long maxretrievable, long concurrentQueries) {
		
		String todo = ("INSERT into `Database` (db_id, url, samplesize, words_number, documents_number, cs_path, index_path, local_path) values " +
				"(" + databaseId + "," + format(url) + "," + sampleSize + "," + words_number + "," + documents_number + "," + format(cs_path) +
				"," + format(index_path) + "," + format(local_path) + "," + maxretrievable + "," + concurrentQueries + ")");

		performTransaction(todo);
		
	}

	public void writeDocument(long documentId, String absolutePath, long db_id) {
		
		String todo = ("INSERT INTO Document (d_id, url, db_id) VALUES (" +
				documentId + "," + format(absolutePath) + "," + db_id + ")");
		
		performTransaction(todo);
	}

	public void writeExtractsFor(int informationExtractorId, int relationId, String attributes, String inferred_attributes) {
		
		String todo = ("INSERT INTO ExtractsFor (ie_id, r_id, attributes, inferred_attributes) VALUES (" + informationExtractorId + "," + relationId + "," + format(attributes) + "," + format(inferred_attributes) +")");
		
		performTransaction(todo);
	}

	public void writeInformationExtractor(int informationExtractorId, String description) {
		
		String todo = ("INSERT INTO InformationExtractor (ie_id, description) VALUES (" + informationExtractorId + "," + format(description) +")");
		
		performTransaction(todo);
		
	}

	public void writeRelation(int relationId, String description) {
		
		String todo = ("INSERT INTO Relation (r_id, description) VALUES (" +
				relationId + "," + format(description) + ")");
		
		performTransaction(todo);
	}

	public ArrayList<String> getProcessedDocuments(String informationExtractorid) {
		//Select d.document.d_id from Document,IEonDocument where d.d_id=ieondoc.d_id and ie.ie_id = informationExtractorid. 
		
		ArrayList<String> res = new ArrayList<String>();
		
		Statement Stmt;
		try {
			Stmt = getConnection().createStatement();
			
			ResultSet RS = Stmt.executeQuery
	        ("SELECT D.d_id " +
	         " FROM Document D, IEonDocument I WHERE D.d_id=I.d_id AND I.ie_id=" + informationExtractorid);

	        while (RS.next()) {
	             res.add(RS.getString(1));
	        }
	        RS.close();
	        Stmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

        return res;
		
	}

	public ArrayList<Tuple> getStoredTuples(String informationExtractorId, Long relationId, Long documentId) {
		
		ArrayList<Tuple> res = new ArrayList<Tuple>();
		
		Statement Stmt;
		try {
			Stmt = getConnection().createStatement();
			
			ResultSet RS = Stmt.executeQuery
	        ("SELECT T.ie_id,T.r_id,T.t_id,T.text,T.troot_ie_id,T.troot_r_id,T.troot_id" +
	         " FROM Tuple T, DocumentTuples DT WHERE DT.r_id=T.r_id AND DT.t_id=T.t_id AND DT.d_id=" + documentId + " AND DT.ie_id=" +
	         informationExtractorId + " AND T.r_id=" + relationId);

	        while (RS.next()) {
	        	int ie_id = Integer.parseInt(RS.getString("T.ie_id"));
	        	int r_id = Integer.parseInt(RS.getString("T.r_id"));
	        	int t_id = Integer.parseInt(RS.getString("T.t_id"));
	        	
	        	String text = RS.getString("T.text");
	        	
	        	try {
	        		int troot_ie_id = Integer.parseInt(RS.getString("T.troot_r_id"));
	        		int troot_r_id = Integer.parseInt(RS.getString("T.troot_r_id"));
	        		int troot_t_id = Integer.parseInt(RS.getString("T.troot_id"));
	        		res.add(new Tuple(ie_id,r_id,t_id,text,troot_ie_id,troot_r_id,troot_t_id));
				} catch (Exception e) {
					res.add(new Tuple(ie_id,r_id,t_id,text,Tuple.NO_ROOT_DETECTED,Tuple.NO_ROOT_DETECTED,Tuple.NO_ROOT_DETECTED));
				}
	        	
	        }
	        
	        RS.close();
	        
	        Stmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

        return res;
	}

	public ArrayList<DataBase> readDatabases(long[] databasesId) {
		
		ArrayList<DataBase> res = new ArrayList<DataBase>();
		
		Statement Stmt;
		try {
			
			for (int i = 0; i < databasesId.length; i++) {
				
				Stmt = getConnection().createStatement();
				
				ResultSet RS = Stmt.executeQuery
		        ("SELECT *" +
		         " FROM `Database` D WHERE D.db_id=" + databasesId[i]);

		        while (RS.next()) {
		            long d_id = Long.parseLong(RS.getString("db_id"));
		        	String url = RS.getString("url");
		        	long samplesize = Integer.parseInt(RS.getString("samplesize"));
		        	long words_number = Integer.parseInt(RS.getString("words_number"));
		        	long documents_number = Integer.parseInt(RS.getString("documents_number"));
		        	String cs_path = RS.getString("cs_path");
		        	String index_path = RS.getString("index_path");
		        	String local_path = RS.getString("local_path");
		        	long maxretrievable = Integer.parseInt(RS.getString("maxretrievable"));
		        	long concurrentQueries = Integer.parseInt(RS.getString("concurrentQueries"));
		        	res.add(new DataBase(d_id,url,samplesize,words_number,documents_number,cs_path,index_path,local_path,maxretrievable,concurrentQueries));
		        }
		        RS.close();
		        Stmt.close();
				
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}

        return res;
	}

	public ArrayList<Query> readQueries(long[] queries) {
		
		ArrayList<Query> res = new ArrayList<Query>();
		
		Statement Stmt;
		try {
			
			for (int i = 0; i < queries.length; i++) {
				
				Stmt = getConnection().createStatement();
				
				ResultSet RS = Stmt.executeQuery
		        ("SELECT Q.* " +
		         " FROM Query Q WHERE Q.q_id=" + queries[i]);
	
		        while (RS.next()) {
		             
		        	long q_id = Integer.parseInt(RS.getString(1));
		        	String text = RS.getString(2);
		        	int ie_id = Integer.parseInt(RS.getString(3));
		        	int r_id = Integer.parseInt(RS.getString(4));
		        	res.add(new Query(q_id,text,ie_id,r_id));
		        }
		        RS.close();
		        Stmt.close();
			}
		    
		    
			
		} catch (SQLException e) {
			e.printStackTrace();
		}

        return res;
	}

	public void writeQuery(long queryId, String text, String informationExtractionId, String relationId) {
		
		String todo = ("INSERT INTO Query (q_id, text, ie_id, r_id) VALUES (" + queryId + "," + format(text) + "," + informationExtractionId + "," + relationId +")");
		
		performTransaction(todo);
		
	}

	public Relation readRelation(long relationID) {
		
		Relation res = null;
		
		Statement Stmt;
		try {
			Stmt = getConnection().createStatement();
			
			ResultSet RS = Stmt.executeQuery
	        ("SELECT R.r_id,R.description " +
	         " FROM Relation R WHERE R.r_id=" + relationID);

	        while (RS.next()) {
	             res = new Relation(RS.getLong(1),RS.getString(2));
	        }
	        RS.close();
	        Stmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

        return res;
	}

	public double getInitialPrecision(long queryId, long databaseId) {
		
		double res = 0;
		
		Statement Stmt;

		try {
			Stmt = getConnection().createStatement();
			
			ResultSet RS = Stmt.executeQuery
	        ("SELECT IP.precision " +
	         " FROM InitialPrecision IP WHERE IP.q_id=" + queryId + " AND IP.db_id=" + databaseId);

	        while (RS.next()) {
	             res = RS.getDouble(1);
	        }
	        RS.close();
	        Stmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

        return res;
		
	}

	public Document getDocument(long databaseId, String url) {
		
		long id = getDocumentId(url,databaseId);
		
		if (id==DOC_NOT_FOUNDED){
			id = getNextDocumentId();
			this.writeDocument(id,url, databaseId);

		}
		
		return new Document(id,url,databaseId);
		
	}

	

	public long getDocumentId(String url, long databaseId) {
		long res = DOC_NOT_FOUNDED;
		
		Statement Stmt;

		try {
			Stmt = getConnection().createStatement();
			
			ResultSet RS = Stmt.executeQuery
	        ("SELECT D.d_id " +
	         " FROM Document D WHERE D.url=" + format(url) + " COLLATE latin1_general_cs AND D.db_id=" + databaseId);

			while (RS.next()) {
	             res = RS.getLong(1);
	        }
	        RS.close();
	        Stmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

        return res;
	}

	public long getNextQueryId() {
		
		long res = 0;
		
		Statement Stmt;

		try {
			Stmt = getConnection().createStatement();
			
			ResultSet RS = Stmt.executeQuery
	        ("SELECT COUNT(Q.q_id) FROM Query Q");

			while (RS.next()) {
				
				res = RS.getLong(1) + 1;
	        
			}
	        RS.close();
	        Stmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return res;
	}

	public long getNextDocumentId() {
		long res = 0;
		
		Statement Stmt;

		try {
			Stmt = getConnection().createStatement();
			
			ResultSet RS = Stmt.executeQuery
	        ("SELECT COUNT(D.d_id) FROM Document D");

			while (RS.next()) {
				
				res = RS.getLong(1) + 1;
	        
			}
	        RS.close();
	        Stmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return res;
	}

	public long getNextTupleId(long informationExtractionId, long relationId) {
		
		long res = 0;
		
		Statement Stmt;

		try {
			Stmt = getConnection().createStatement();
			
			ResultSet RS = Stmt.executeQuery
	        ("SELECT COUNT(T.t_id) FROM Tuple T WHERE T.ie_id=" + informationExtractionId + " AND T.r_id=" + relationId);

			while (RS.next()) {
				
				res = RS.getLong(1) + 1;
	        
			}
	        RS.close();
	        Stmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return res;
		
	}

	public boolean hasDocumentBeenProcessed(long documentId,
			String informationExtractionId) {
		boolean res = false;
		
		Statement Stmt;

		try {
			Stmt = getConnection().createStatement();
			
			ResultSet RS = Stmt.executeQuery
	        ("SELECT ID.* " +
	         " FROM IEonDocument ID WHERE ID.d_id=" + documentId + " AND ID.ie_id=" + informationExtractionId);

	        res = RS.next();
	        	
	        RS.close();
	        Stmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

        return res;
	}
	
}
